/**
 * @author:fangxiaohua
*  @邮箱：abc2710712@qq.com
*  @qq:1295168875
*  node mysql操作
*/

void(function(exports){
	 var mysql  = require('mysql');  //调用MySQL模块
	 var dbConfig = require('./contantsLib').DB_CONFIG;//数据库连接常量
     
	 var connection;
     
     //数据库连接
     var dbConnection=exports.dbConnection = function(){
    	 		if(connection){
    	 			 return connection;
    	 		}else{
	    	 			//创建数据库连接
	 				   var connection = mysql.createConnection({    
	 					    	  host:dbConfig.host,           			                 //主机
	 					    	  port:dbConfig.port,                   		     		 //端口号
	 					    	  database:dbConfig.database,                   //数据库
	 					    	  charset:dbConfig.charset,	                        //连接字符编码
	 					    	  user:dbConfig.user,               		     		    //MySQL认证用户名
	 					    	  password:dbConfig.password 					//密码
	 				    });
	 		    	//数据库连接
	 		    	  connection.connect(function(err){
	 		    	    	  if(err){       
	 		    		    	  console.log('[query] - :'+err);
	 		    		    	  return;
	 		    	    	  }
	 		    	    	  console.log('[数据库连接成功]  succeed!');
	 		    	  	}); 
	 		    	  return connection;
    	 		}
      };
	  
        
	  //查询
	  var dbQuery=exports.dbQuery=function(sql,params,callback){
			  var conn=dbConnection();
			  conn.query(sql,params,function (err, result){
				  if(err){       
    		    	  console.log("数据库查询失败!");
    		    	  return;
    	    	  }
				  if(typeof callback =="function"){
					  callback(err,result);
				  };
				  console.log('--------------------------SELECT----------------------------');
				  console.log(result);       
				  console.log('---------------------------------------------------------------');
			  });
	  };
	  
	  //分页查询查询
	  var dbPageQuery=exports.dbPageQuery=function(sql,pageNo,pageSize,params,callback){
			  var conn=dbConnection();
			 var pageView={"records":[],"totalpage":1,"pageNo":pageNo?pageNo:1,"pageSize":pageSize?pageSize:10,"totalrecord":0,"userdata":null};
			  //查询总条数
			  var countQueryString = "select count(*) as count from (" + sql + ") t";
			  console.log("countQueryString:"+countQueryString);
			  conn.query(countQueryString,params,function (err, result){
				  if(err){       
    		    	  console.log("数据库查询失败!");
    		    	  return;
    	    	  }
				  var totalCount=result[0].count;//数据总条数
				  console.log("totalCount"+"========"+totalCount);
				  //重置pageView基本信息
				  pageView.totalpage=Number.parseInt(totalCount)%Number.parseInt(pageSize)==0?Number.parseInt(totalCount)/Number.parseInt(pageSize):Number.parseInt(totalCount)/Number.parseInt(pageSize)+1
				  pageView.totalrecord=totalCount;
				  console.log("totalCount:"+totalCount);
				  if (Number.parseInt(totalCount) < 1) {
					  if(typeof callback =="function"){
						  callback(err,pageView);
					  };
					  return;
				  }else{
					  //分页查询
					  var  pageQueryString = "select * from (" + sql + ") t LIMIT "+ ((Number.parseInt(pageNo)-1)*Number.parseInt(pageSize)) + "," + Number.parseInt(pageSize) + "";
					  console.log("pageQueryString:"+pageQueryString);
					  conn.query(pageQueryString,params,function (err, result){
						 if(err){       
		    		    	  console.log(err);
		    		    	  return;
		    	    	  }
						  //将查询数据赋予pageView
						  pageView.records=result;
						  if(typeof callback =="function"){
							  callback(err,pageView);
						  };
					  });
				  }
			  });
	  };
	  
	  //添加
	  var dbAdd=exports.dbAdd=function(sql,params,callback){
			  var conn=dbConnection();
			  conn.query(sql,params,function (err, result){
				  if(err){       
    		    	  console.log("数据库添加失败!");
    		    	  return;
    	    	  }
				  if(typeof callback =="function"){
					  callback(err,result);
				  };
				  console.log('--------------------------INSERT----------------------------');
				  console.log('INSERT ID:',result.insertId);       
				  console.log('------------------------------------------------------------------');
			  });
	  };
	  
	  //修改
	  var dbUpdate=exports.dbUpdate=function(sql,params,callback){
			  var conn=dbConnection();
			  conn.query(sql,params,function (err, result){
				  if(err){       
    		    	  console.log("数据库修改失败!");
    		    	  return;
    	    	  }
				  if(typeof callback =="function"){
					  callback(err,result);
				  };
				  console.log('--------------------------UPDATE----------------------------');
				  console.log('UPDATE affectedRows',result.affectedRows);
				  console.log('-------------------------------------------------------------------');
			  });
	  };
	  
	  //删除
	  var dbDelete=exports.dbDelete=function(sql,params,callback){
			  var conn=dbConnection();
			  conn.query(sql,params,function (err, result){
				  if(err){       
    		    	  console.log("数据库删除失败!");
    		    	  return;
    	    	  }
				  if(typeof callback =="function"){
					  callback(err,result);
				  };
				  console.log('--------------------------DELETE----------------------------');
				  console.log('DELETE affectedRows',result.affectedRows);
				  console.log('----------------------------------------------------------------');
			  });
	  }
})( (function(){
    if(typeof exports === 'undefined') {
        window.exports = {};
        return window.exports;
    } else {
        return exports;
    }
})());